//入口文件
const express = require('express');
//hbs模板
const hbs = require('express-hbs');
//引入session
const session = require('express-session');
//引入flash
const flash = require('connect-flash');
//初始化
const app = express();
//引入参数解析
const bodyParser = require('body-parser');
//引入mysql
const mysql = require('mysql');
//创建一个连接池
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'node_test'
});
/* //连接mysql
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'node_test'
}); */


app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: false}))

//设置session
app.use(session({
    secret: 'keyboard',
    resave: false,
    saveUninitialized: true,
    // cookie: { secure: true }这个是开启https才能储存cookie
}))
//设置layout
app.engine('hbs', hbs.express4({
    defaultLayout:  __dirname + '/views/layouts/main',
}));
//设置引擎
app.set('view engine', 'hbs');
//设置静态资源路径
app.use(express.static('public'));
//设置flash
app.use(flash());
app.use((req, res, next) => {
    res.locals.status = req.flash('status');
    res.locals.cont = req.flash('cont');
    next();
});
//学生列表
app.get('/', (req, res) => {
    pool.getConnection((err, conn) => {
        if (err) {
            req.flash('status', 'danger');
            req.flash('cont', '连接失败');
            res.redirect('/');
        }
        conn.query("SELECT * FROM users", function(err, result) {
            res.render('index', {list: result});
        });
    });
   
});

//测试flash
app.get('/flash', (req, res) => {
    req.flash('status', 'success');
    req.flash('cont', 'flash配置成功! 恭喜!');
    res.redirect('/');
});
//加载添加视图
app.get('/add', (req, res) => {
    res.render('add');
});

//执行添加
app.post('/add', (req, res) => {
    //从连接池获取一个连接
    pool.getConnection((err, conn) => {

        if (err) {
            console.log(err);
            req.flash('status', 'danger');
            req.flash('cont', '连接失败');
            res.redirect('/');
        } else {
            conn.query(`INSERT INTO users(name, class_name, hobby) VALUES('${req.body.name}','${req.body.class_name}','${req.body.hobby}')`, function(err, result) {
                if (err) {
                    console.log(err);
                    req.flash('status', 'danger');
                    req.flash('cont', '添加失败');
                } else {
                    req.flash('status', 'success');
                    req.flash('cont', '添加成功');
                }

                res.redirect('/');
            });
        }
    });
    // console.log(req.body);
});

//加载修改视图
app.get('/edit/:id', (req, res) => {
    pool.getConnection((err, conn) => {
        if (err) {

        } else {
            conn.query(`SELECT * FROM users WHERE id = ${req.params.id}`, (err, result) => {
                let dataString = JSON.stringify(result);
                let ret = JSON.parse(dataString);
                res.render('edit', ret[0]);
            });
        }
    });
});

//执行修改视图
app.post('/edit', (req, res) => {
    pool.getConnection((err, conn) => {
        conn.query(`UPDATE users SET name='${req.body.name}',class_name='${req.body.class_name}',hobby='${req.body.hobby}' WHERE id = ${req.body.id}`, (err, result) => {
            req.flash("status", "success");
            req.flash("cont", '编辑成功');
            res.redirect("/");
        });
        //conn.query(`INSERT INTO users(name, class_name, hobby) VALUES('${req.body.name}','${req.body.class_name}','${req.body.hobby}')`,
    });
    
});

//删除数据
app.get('/del/:id', (req, res) => {
    if (req.params.id) {
        pool.getConnection((err, conn) => {
            conn.query(`DELETE FROM users WHERE id = ${req.params.id}`, (err, result) => {
                if (err) {
                    req.flash('status', 'danger');
                    req.flash('cont', '删除失败');
                } else {
                    req.flash('status', 'success');
                    req.flash('cont', '删除成功');
                }

                res.redirect('/');
            });
        });
    } else {
        req.flash('status', 'danger');
        req.flash('cont', '参数错误');
        res.redirect("/");
    }
});

app.listen(3000);
